package com.ufest.util;
import java.io.BufferedInputStream;  
import java.io.FileInputStream;  
import java.io.InputStream;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.Properties;  
  
/** 
 * @author 
 *  
 */  
public class JdbcByPropertiesUtil {  
    private static String filePath = "jdbc.properties";   
    private static JdbcByPropertiesUtil instance = null;  
      
    public JdbcByPropertiesUtil() {  
        super();  
    }  
  
    /** 

     * @return 
     */  
    public static JdbcByPropertiesUtil getInstance() {  
        if (instance == null) {  
            synchronized (JdbcByPropertiesUtil.class) {  
                if (instance == null) {  
                    instance = new JdbcByPropertiesUtil();  
                }  
            }  
        }  
        return instance;  
    }  
         
    /** 
     * @param filePath 
     * add 2012-4-17 
     */  
    public static Properties readPropertiesFile(){  
        String realFilePath = Thread.currentThread().getContextClassLoader().getResource("").getPath()+filePath;   
        Properties pros = new Properties();    
        try {    
            InputStream is = new BufferedInputStream(new FileInputStream(realFilePath));    
            pros.load(is);   
        } catch (Exception e) {    
            e.printStackTrace();  
        }  
        return pros;  
    }   
      

    static{  
        Properties pros = readPropertiesFile();  
        String className = (String) pros.get("className");  
        try {  
            Class.forName(className).newInstance();  
        } catch (Exception e) {  
            e.printStackTrace();  
        }   
    }  
      
    /** 
     * modify 2012-4-17 
     * @param con 
     * @return 
     */  
    public Connection getConnection(){  
        Properties pros = readPropertiesFile();  
        String url = (String) pros.get("url");  
        String user = (String) pros.get("user");  
        String password = (String) pros.get("password");  
        Connection conn = null;  
        try {  
            conn = DriverManager.getConnection(url,user,password);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return conn;  
    }  
         
    /** 
     * @param rs 
     * @param st 
     * @param pst 
     * @param conn 
     */  
    public void close(ResultSet rs,Statement st,Connection conn){  
        if(rs != null){  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            } finally{  
                if(st != null){  
                    try {  
                        st.close();  
                    } catch (SQLException e) {  
                        e.printStackTrace();  
                    } finally{  
                        if(conn != null){  
                            try {  
                                conn.close();  
                            } catch (SQLException e) {  
                                e.printStackTrace();  
                            }  
                        }  
                    }  
                }  
            }  
        }  
    }  
  
  
    /** 
     * @param sql 
     * @throws   
     */  
    public void execute(String sql){  
        JdbcByPropertiesUtil jbpu = getInstance();  
        Connection conn = null;  
        PreparedStatement pst = null;  
        try {  
            conn = jbpu.getConnection();  
            conn.setAutoCommit(false);  
            pst = conn.prepareStatement(sql);  
            pst.execute();  
            conn.commit();  
        } catch (Exception e) {  
            try {  
                conn.rollback();  
            } catch (SQLException e1) {  
                e1.printStackTrace();  
            }  
            e.printStackTrace();  
        } finally{  
            //Statement st = null;  
            ResultSet rs = null;  
            jbpu.close(rs, pst, conn);   
        }  
    }  
    /** 
     * 
     * @param sql 
     * @throws   
     */  
    public ResultSet getData(String sql){  
        JdbcByPropertiesUtil jbpu = getInstance();  
        Connection conn = null;  
        
        Statement stmt = null;
                  
        ResultSet result = null;
                  
         
                  
        try {
        	conn = jbpu.getConnection();
        	conn.setAutoCommit(false);   
        	
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);//tatement�ӿ���Ҫͨ��connection�ӿڽ���ʵ�����   
			         
			result = stmt.executeQuery(sql);
			conn.commit();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{ 
			
			 jbpu.close(null, stmt, conn);  
			
        }
		
		return result;
    } 
   
    /** 
     * @param sql 
     * @throws   
     */  
    public void executeUpdate(String sql) {  
        JdbcByPropertiesUtil jbpu = getInstance();  
        Connection conn = null;  
        PreparedStatement pst = null;  
        try {  
            conn = jbpu.getConnection();  
            conn.setAutoCommit(false);  
            pst = conn.prepareStatement(sql);  
            pst.executeUpdate();  
            conn.commit();  
        } catch (Exception e) {  
            try {  
                conn.rollback();  
            } catch (SQLException e1) {  
                e1.printStackTrace();  
            }  
            e.printStackTrace();  
        } finally{  
            //Statement st = null;  
            ResultSet rs = null;  
            jbpu.close(rs, pst, conn);   
        }  
    }  
      
      
}  
